﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using System.Data.SQLite;

namespace DBToExcel.Code
{
    public class SQLiteHelper
    {
        //private string DBPath = Path.Combine(Environment.CurrentDirectory, "sqlbuilderdb");

        private string _DBName = string.Empty;
        private string _DBPwd = string.Empty;
        private string _DBPath = string.Empty;
        private string _DBFileName = string.Empty;
        private static SQLiteHelper _Current = null;
        private string _CreatTableSql = string.Empty;

        public string DBName { get { return this._DBName; } }

        public string DBPwd { get { return this._DBPwd; } }

        public string DBPath { get { return this._DBPath; } }

        public string DBFileName { get { return this.DBFileName; } }

        public static SQLiteHelper Current
        {
            get
            {
                return _Current;
            }
        }

        public SQLiteHelper()
        {
            this._DBName = "excelbuilder.db";
            this._DBPwd = "5201314wll";
            this._DBPath = Environment.CurrentDirectory;
            this._DBFileName = Path.Combine(Environment.CurrentDirectory, _DBName);
            _Current = this;
            StringBuilder sb = new StringBuilder();
            
            sb.Append("CREATE TABLE IF NOT EXISTS template(Id TEXT PRIMARY KEY NOT NULL,OrgFileName TEXT  NOT NULL,  Name TEXT NOT NULL,ConnId TEXT NOT NULL, DataSql TEXT NOT NULL, TempPlateExcelFile TEXT NOT NULL, StartRowIndex TEXT NOT NULL,IsTempLate TEXT NOT NULL);");
            sb.Append("CREATE TABLE IF NOT EXISTS dbconn(Id TEXT PRIMARY KEY NOT NULL,Name TEXT  KEY NOT NULL, FileName TEXT NOT NULL, ClassName TEXT NOT NULL,ConnStr TEXT NOT NULL);");
            sb.Append("CREATE TABLE IF NOT EXISTS excelmap(Id TEXT PRIMARY KEY NOT NULL,TemplateId TEXT NOT NULL,TempValue TEXT NOT NULL,  FieldName text not null,ExcelCellIndex text not null,Script text not null);");
            _CreatTableSql = sb.ToString();
        }



        public bool InitDB()
        {
            if (!IsHasDB())
            {
                CreatDB();
            }


            CreatTable();
            return true;

        }

        private bool IsHasDB()
        {
            return File.Exists(this._DBFileName);
        }


        private void CreatDB()
        {
            SQLiteConnection.CreateFile(this._DBFileName);
            //连接数据库  
            SQLiteConnection conn = (SQLiteConnection)GetConn();
            conn.ChangePassword(this._DBPwd);
        }

        private void CreatTable()
        {
            IDbConnection conn = GetConn();
            conn.Execute(this._CreatTableSql);
        }

        public IDbConnection GetConn()
        {
            SQLiteConnection conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
            connstr.DataSource = this._DBFileName;
            connstr.Password = this._DBPwd;//设置密码，SQLite ADO.NET实现了数据库密码保护  
            conn.ConnectionString = connstr.ToString();
            conn.Open();

            return conn;
        }
    }
}
